/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package anivis.db;

import anivis.base.DataAvailableNotification;
import java.sql.ResultSet;
import java.util.Set;

/**
 *
 * @author siberion
 */
public class QueryLayer extends java.util.Observable
{
    public enum RestrictionType
    {
        None,
        TimeWindow,
        TimeSpan,
        TemporalAggregation,
        Latitude,
        Longitude,
        SpatialAggregation,
        NominalFilter
    }
    
    public enum DatasetAttribute
    {
        Country,
        StateProvince,
        Locality,
        Species
    }
    
    public enum SpeciesAttribute
    {
        CommonName,
        ScientificName,
        Kingdom,
        Phylum,
        Class,
        Order,
        Family,
        Genus,
        Epithet
    }
    
    public static abstract class Restriction
    {
        RestrictionType t;
        
        public Restriction() {
            this.t = RestrictionType.None;
        }
        
        public Restriction(RestrictionType t) {
            this.t = t;
        }
        
        public RestrictionType getType() {
            return t;
        }

    }
    
    public static class RangeRestriction extends Restriction
    {
        double lo, hi;
        
        public RangeRestriction(double lo, double hi, RestrictionType t) {
            super(t);
            this.lo = lo;
            this.hi = hi;
        }

    }
    
    public static class SetRestriction extends Restriction
    {
        DatasetAttribute attr;
        Set<String> values;
        
        public SetRestriction(DatasetAttribute attr, Set<String> values) {
            super(RestrictionType.NominalFilter);
            this.attr = attr;
            this.values = values;
        }

    }
    
    public static class Aggregation extends Restriction
    {
        double gridsize;
        
        public Aggregation(double gridsize, RestrictionType t) {
            super(t);
            this.gridsize = gridsize;
        }

    }
    
    static class QueryInfo
    {
        QueryLayer _parent;
        String _query;
        double gridsize;
        boolean aggregate;
        DataAvailableNotification _notification;
    }
    
    static class AsynchronousQuery implements java.lang.Runnable
    {
        QueryInfo _info;
        public AsynchronousQuery(QueryInfo info)
        {
            _info = info;
        }
        
        public void run()
        {
            prefuse.data.Table _lastQueryWin, _lastQuerySpan;
            int c_lat, c_long, c_agg, c_date, c_spci, row_num;
            try
            {
                java.sql.Statement stmt = _info._parent._con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                java.sql.ResultSet res = stmt.executeQuery(_info._query);
                
                _lastQueryWin = new prefuse.data.Table();
                _lastQueryWin.addColumn("latitude", double.class);
                _lastQueryWin.addColumn("longitude", double.class);
                _lastQueryWin.addColumn("aggregation", int.class, 0);
                _lastQueryWin.addColumn("date", long.class);
                _lastQueryWin.addColumn("species", int.class);

                _lastQuerySpan = new prefuse.data.Table();
                _lastQuerySpan.addColumn("latitude", double.class);
                _lastQuerySpan.addColumn("longitude", double.class);
                _lastQuerySpan.addColumn("aggregation", int.class, 0);
                _lastQuerySpan.addColumn("date", long.class);
                _lastQuerySpan.addColumn("species", int.class);

                c_lat  = _lastQueryWin.getColumnNumber("latitude");
                c_long = _lastQueryWin.getColumnNumber("longitude");
                c_agg  = _lastQueryWin.getColumnNumber("aggregation");
                c_date = _lastQueryWin.getColumnNumber("date");
                c_spci = _lastQueryWin.getColumnNumber("species");

                while (res.next())
                {
                    if (res.getBoolean(5))
                    {
                        row_num = _lastQueryWin.addRow();
                        _lastQueryWin.set(row_num, c_lat,  res.getObject(1));
                        _lastQueryWin.set(row_num, c_long, res.getObject(2));
                        _lastQueryWin.set(row_num, c_date, res.getObject(3));
                        _lastQueryWin.set(row_num, c_spci, res.getObject(4));
                    }
                    row_num = _lastQuerySpan.addRow();
                    _lastQuerySpan.set(row_num, c_lat,  res.getObject(1));
                    _lastQuerySpan.set(row_num, c_long, res.getObject(2));
                    _lastQuerySpan.set(row_num, c_date, res.getObject(3));
                    _lastQuerySpan.set(row_num, c_spci, res.getObject(4));
                }
                res.close();
                
                _info._parent._lastQuerySpan = _lastQuerySpan;
                _info._parent._lastQueryWin = _lastQueryWin;
                
                if (_info.aggregate) _info._parent._aggregatedData = _info._parent.spatialAggregation(_info.gridsize, _lastQueryWin);
            }
            catch(java.sql.SQLException se)
            {
                return;
            }
            
            _info._parent.setChanged();
            _info._parent.notifyObservers(_info._notification);
        }
    }
    
    public QueryLayer() throws java.sql.SQLException, ClassNotFoundException
    {
        String url = "jdbc:mysql://localhost:3306/anivis";
        java.sql.ResultSet res = null;
        
        Class.forName("com.mysql.jdbc.Driver");
        _con = java.sql.DriverManager.getConnection(url, "anivis", "anivisdb239847");
        _stmt = _con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        res = _stmt.executeQuery("SELECT COUNT(*) FROM dataset_pri;");
        res.next();
        _total = res.getInt(1);
        
        _fields = new java.util.HashMap<DatasetAttribute, String>();
        _fields.put(DatasetAttribute.Country, "Country");
        _fields.put(DatasetAttribute.StateProvince, "StateProvince");
        _fields.put(DatasetAttribute.Locality, "Locality");
        _fields.put(DatasetAttribute.Species, "species_id");
        
        _auxfields = new java.util.HashMap<SpeciesAttribute, String>();
        _auxfields.put(SpeciesAttribute.CommonName, "Common_Name");
        _auxfields.put(SpeciesAttribute.ScientificName, "Scientific_Name");
        _auxfields.put(SpeciesAttribute.Kingdom, "Kingdom");
        _auxfields.put(SpeciesAttribute.Phylum, "Phylum");
        _auxfields.put(SpeciesAttribute.Class, "Class");
        _auxfields.put(SpeciesAttribute.Order, "Order");
        _auxfields.put(SpeciesAttribute.Family, "Family");
        _auxfields.put(SpeciesAttribute.Genus, "Genus");
        _auxfields.put(SpeciesAttribute.Epithet, "Epithet");
        
        _reQuery = true;
        _reAggregate = true;
        
        _timeSpan = null;
        _timeWin = null;
        _latRange = null;
        _longRange = null;
        
        _filters = new java.util.HashMap<DatasetAttribute, SetRestriction>();
    }
    
    /**
     * Impose a restriction on further queries; will overwrite existing restrictions of the same type
     * @param r The restriction object
     * @return Always true - no failure condition
     */
    public boolean setRestriction(Restriction r)
    {
        switch (r.t)
        {
            case TimeSpan:
                _timeSpanNew = RangeRestriction.class.cast(r);
                break;
            case TimeWindow:
                _timeWinNew = RangeRestriction.class.cast(r);
                break;
            case Latitude:
                _latRangeNew = RangeRestriction.class.cast(r);
                break;
            case Longitude:
                _longRangeNew = RangeRestriction.class.cast(r);
                break;
            case TemporalAggregation:
                _tempAgg = Aggregation.class.cast(r);
                _reAggregate = true;
                break;
            case SpatialAggregation:
                _spatAgg = Aggregation.class.cast(r);
                _reAggregate = true;
                break;
            case NominalFilter:
                _filters.put(SetRestriction.class.cast(r).attr, SetRestriction.class.cast(r));
                _reQuery = true;
                _reAggregate = true;
                break;
        }
        return true;
    }
    
    /**
     * Remove the restriction identified by t, if one has been specified. Warning:
     * Specifying RestrictionType::NominalFilter clears all filters of that type.
     * @param t
     * @return true
     */
    public boolean clearRestriction(RestrictionType t)
    {
        switch (t)
        {
            case TimeSpan:
                _timeSpanNew = null;
                break;
            case TimeWindow:
                _timeWinNew = null;
                break;
            case Latitude:
                _latRangeNew = null;
                break;
            case Longitude:
                _longRangeNew = null;
                break;
            case TemporalAggregation:
                if (_tempAgg != null)
                {
                   _tempAgg = null;
                   _reAggregate = true;
                }
                return true;
            case SpatialAggregation:
                if (_spatAgg != null)
                {
                    _spatAgg = null;
                    _reAggregate = true;
                }
                return true;
            case NominalFilter:
                _filters.clear();
                _reQuery = true;
                _reAggregate = true;
                break;
        }
        return true;
    }
    
    /**
     * Remove restrictions of type NominalFilter individually.
     * @param a Attribute to remove the filter for
     * @return true
     */
    public boolean clearFilter(DatasetAttribute a)
    {
        _filters.remove(a);
        _reQuery = true;
        _reAggregate = true;
        return true;
    }
    
    /**
     * Prepares data as indicated by filters specified through set/clearRestriction
     * @param notification Arbitrary object to use for notification
     * @return Always true
     */
    public boolean prepareData() throws java.sql.SQLException
    {
        java.util.Set<RestrictionType> r = new java.util.HashSet<RestrictionType>();
        QueryInfo info = new QueryInfo();
        AsynchronousQuery aq;
        
        if (qthread != null)
        {
            qthread.interrupt();
            try { qthread.join(); } catch (InterruptedException ex) {}
        }
        
        info._parent = this;
        info.aggregate = (_spatAgg != null);
        if (info.aggregate) info.gridsize = _spatAgg.gridsize;
        info._query = updateQuery();
        
        if (_timeSpan != null) r.add(RestrictionType.TimeSpan);
        if (_timeWin != null) r.add(RestrictionType.TimeWindow);
        if (_latRange != null) r.add(RestrictionType.Latitude);
        if (_longRange != null) r.add(RestrictionType.Longitude);
        if (_filters.size() > 0) r.add(RestrictionType.NominalFilter);
        if (_spatAgg != null) r.add(RestrictionType.SpatialAggregation);
        if (_tempAgg != null) r.add(RestrictionType.TemporalAggregation);

        info._notification = new DataAvailableNotification(r);
        aq = new AsynchronousQuery(info);
        qthread = new Thread(aq);
        qthread.run();
        
        return true;
    }
    
    /**
     * Retrieve number of records in a filter
     * @param r Filters to apply to data
     * @return Number of records
     * @throws java.sql.SQLException
     */
    public int getDataCount(java.util.Set<RestrictionType> r) throws java.sql.SQLException
    {
        boolean sagg, tagg, window;
        prefuse.data.Table t;
        
        sagg = r.contains(RestrictionType.SpatialAggregation);
        tagg = r.contains(RestrictionType.TemporalAggregation);
        window = r.contains(RestrictionType.TimeWindow);
        
        if (sagg && _spatAgg != null) t = _aggregatedData;
        //else if (tagg && _tempAgg != null) t = temporalAggregation(_spatAgg.gridsize, (window ? _lastQueryWin : _lastQuerySpan));
        else t = (window ? _lastQueryWin : _lastQuerySpan);
        
        return t.getRowCount();
    }
    
    /**
     * Retrieve filtered set of records from dataset
     * @param r Filters to apply to data
     * @param t Table to receive result (prior contents lost!)
     * @return Number of records returned
     * @throws java.sql.SQLException
     */
    public prefuse.data.Table getData(java.util.Set<RestrictionType> r) throws java.sql.SQLException
    {
        boolean sagg, tagg, window;
        
        sagg = r.contains(RestrictionType.SpatialAggregation);
        tagg = r.contains(RestrictionType.TemporalAggregation);
        window = r.contains(RestrictionType.TimeWindow);
        
        if (sagg && _spatAgg != null) return _aggregatedData;
        //else if (tagg && _tempAgg != null) t = temporalAggregation(_spatAgg.gridsize, (window ? _lastQueryWin : _lastQuerySpan));
        else return (window ? _lastQueryWin : _lastQuerySpan);
    }
    
    /**
     * Retrieve set of possible values for a nominal attribute
     * @param a Attribute to look at
     * @return Set of possible values
     * @throws java.sql.SQLException
     */
    public java.util.Set<String> getDistinctValues(DatasetAttribute a) throws java.sql.SQLException
    {
        java.util.Set<String> values = new java.util.HashSet<String>();
        java.sql.ResultSet res = null;
        String s, query;

        query = "SELECT DISTINCT `" + _fields.get(a) + "` FROM dataset_pri ORDER BY `" + _fields.get(a) + "` ASC";
        System.out.println(query);
        res = _stmt.executeQuery(query);
        
        while (res.next())
        {
            s = res.getString(1);
            if (!res.wasNull()) values.add(s);
        }
        res.close();
        
        return values;
    }
    
    public java.util.Set<String> getDistinctValues(SpeciesAttribute a) throws java.sql.SQLException
    {
        java.util.Set<String> values = new java.util.HashSet<String>();
        java.sql.ResultSet res = null;
        String s, query;

        query = "SELECT DISTINCT `" + _auxfields.get(a) + "` FROM dataset_sec ORDER BY `" + _auxfields.get(a) + "` ASC";
        System.out.println(query);
        res = _stmt.executeQuery(query);
        
        while (res.next())
        {
            s = res.getString(1);
            if (!res.wasNull()) values.add(s);
        }
        res.close();
        
        return values;
    }

    private java.sql.Connection _con;
    private java.sql.Statement _stmt;
    private int _total;
    private java.util.HashMap<DatasetAttribute, String> _fields;
    private java.util.HashMap<SpeciesAttribute, String> _auxfields;
    private prefuse.data.Table _lastQueryWin, _lastQuerySpan, _aggregatedData;
    private boolean _reQuery, _reAggregate;
    
    private RangeRestriction _timeSpan, _timeSpanNew, _timeWin, _timeWinNew,
                             _latRange, _latRangeNew, _longRange, _longRangeNew;
    private Aggregation _tempAgg, _spatAgg;
    private java.util.HashMap<DatasetAttribute, SetRestriction> _filters;
    
    private java.lang.Thread qthread;
    
    /**
     * Private function to formulate a query based on a filter
     * @param fields String identifying fields to return from SQL SELECT
     * @param r Filter to apply
     * @return The query
     */
    private String BuildQuery(String fields, java.util.Set<RestrictionType> r)
    {
        String query = "SELECT " + fields + " FROM dataset_pri WHERE 1";
        java.util.Iterator<String> j;
        java.util.Iterator<DatasetAttribute> k;
        Restriction rstr;
        RangeRestriction rr;
        SetRestriction sr;
        Aggregation ar;
        boolean firstel;
        String str = null;
        double gridsize = 0.0;
        
        if (_timeSpan != null && r.contains(RestrictionType.TimeSpan)) query += " AND `Date_Collected` >= " + _timeSpan.lo + " AND `Date_Collected` <= " + _timeSpan.hi;
        if (_latRange != null && r.contains(RestrictionType.Latitude)) query += " AND `Decimal_Latitude` >= " + _latRange.lo + " AND `Decimal_Latitude` <= " + _latRange.hi;
        if (_longRange != null && r.contains(RestrictionType.Longitude)) query += " AND `Decimal_Longitude` >= " + _longRange.lo + " AND `Decimal_Longitude` <= " + _longRange.hi;
        
        k = _filters.keySet().iterator();
        while (k.hasNext() && r.contains(RestrictionType.NominalFilter))
        {
            sr = _filters.get(k.next());
            j = sr.values.iterator();
            firstel = true;
            while (j.hasNext())
            {
                if (firstel)
                {
                    query += " AND `" + _fields.get(sr.attr) + "` IN (";
                    firstel = false;
                }
                else query += ",";
                str = j.next();
                str.replace("'", "!^");
                if (sr.attr == DatasetAttribute.Species) query += str;
                else query += "'" + str + "'";
            }
            query += ")";
        }
        
        if (_spatAgg != null) query += " ORDER BY (FLOOR(Decimal_Longitude / "
                                  + _spatAgg.gridsize + ") * 500) + Decimal_Latitude ASC";
        
        return query + ";";
    }
    
    private String updateQuery()
    {
        String query = null;
        java.sql.ResultSet res = null;
        double winlo, winhi;
        java.util.HashSet<RestrictionType> restriction = new java.util.HashSet<RestrictionType>();
        
        if (_timeSpan  != _timeSpanNew ) _reQuery = true;
        if (_timeWin   != _timeWinNew  ) _reQuery = true;
        if (_latRange  != _latRangeNew ) _reQuery = true;
        if (_longRange != _longRangeNew) _reQuery = true;
        
        if (!_reQuery) return null;
        
        if (_timeSpan  != _timeSpanNew ) _timeSpan  = _timeSpanNew;
        if (_timeWin   != _timeWinNew  ) _timeWin   = _timeWinNew;
        if (_latRange  != _latRangeNew ) _latRange  = _latRangeNew;
        if (_longRange != _longRangeNew) _longRange = _longRangeNew;
        
        if (_timeWin == null)
        {
            if (_timeSpan == null)
            {
                winlo = 0;
                winhi = 1202770800;
            }
            else
            {
                winlo = _timeSpan.lo;
                winhi = _timeSpan.hi;
            }
        }
        else
        {
            winlo = _timeWin.lo;
            winhi = _timeWin.hi;
        }

        restriction.add(RestrictionType.TimeSpan);
        restriction.add(RestrictionType.Latitude);
        restriction.add(RestrictionType.Longitude);
        restriction.add(RestrictionType.NominalFilter);
        query = BuildQuery("Decimal_Latitude,Decimal_Longitude,Date_Collected,Species_Id,"
                + "(Date_Collected >= " + winlo + " AND Date_Collected <= " + winhi + ") AS InWindow", restriction);
        System.out.println(query);
        _reQuery = false;
        
        return query;
    }
    
    private prefuse.data.Table temporalAggregation(double grid, prefuse.data.Table in)
    {
        return in;
    }
    
    /**
     * Combine data points that are located in the same cell of some grid. Be aware that for this
     * function to work correctly, the data points MUST be ordered such that all the data points
     * within one cell appear in a consecutive sequence in the input table.
     * @param grid
     * @param in
     * @return
     */
    private prefuse.data.Table spatialAggregation(double grid, prefuse.data.Table in)
    {
        int count = 0, gx = -1000, gy = -1000, r, newrow, tuplecount;
        double dlat = 0.0, dlong = 0.0, dlatavg = 0.0, dlongavg = 0.0;
        
        if (grid <= 1.0) return in;
        
        prefuse.data.Table out = new prefuse.data.Table();
        out.addColumns(in.getSchema());
        out.removeColumn("date");
        
        for (r = 1; in.isValidRow(r); ++r)
        {
            dlat = in.getDouble(r, "latitude");
            dlong = in.getDouble(r, "longitude");
            
            if (gx != java.lang.Math.floor(dlat / grid) || gy != java.lang.Math.floor(dlong / grid))
            {
                if (count > 0)
                {
                    newrow = out.addRow();
                    out.setDouble(newrow, "latitude", dlatavg / count);
                    out.setDouble(newrow, "longitude", dlongavg / count);
                    out.setInt(newrow, "aggregation", count);
                    //out.setInt(newrow, "species", in.getInt(r, "species"));
                }
                dlatavg = 0.0;
                dlongavg = 0.0;
                count = 0;
                gx = (int)java.lang.Math.floor(dlat / grid);
                gy = (int)java.lang.Math.floor(dlong / grid);
            }
            
            dlatavg += dlat;
            dlongavg += dlong;
            ++count;
        }
        
        if (count > 0)
        {
            newrow = out.addRow();
            out.setDouble(newrow, "latitude", dlatavg / count);
            out.setDouble(newrow, "longitude", dlongavg / count);
            out.setInt(newrow, "aggregation", count);
            //out.setInt(newrow, "species", in.getInt(r, "species"));
        }
        
        return out;
    }
}
